import requests
import re
import time
import pymysql
import random



def find_keyword_web(cur):
    key_word_link={} #key_word_link:{key1:{aim1:{1:***,2:***,3:***},aim2:{***}},key2:{}}，1,2,3代表页数
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/103.0.0.0 Safari/537.36',
        'Cookie':'BIDUPSID=F6EFA7BB4FFE677CAA0C7683E4DE8344; PSTM=1623385735; __yjs_duid=1_45ed5ec2b452a0376eae1cf744418bcd1623386438995; BD_UPN=12314753; BDUSS=0VRc296dDA3cWdHSlJGUGd4ckhtd2JHT1hYZEpFRHFNQkJneXYzNmNWYUdmcVZpRVFBQUFBJCQAAAAAAAAAAAEAAABgjjykemMyMzM1OTYwMTMxAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAIbxfWKG8X1iV; BDUSS_BFESS=0VRc296dDA3cWdHSlJGUGd4ckhtd2JHT1hYZEpFRHFNQkJneXYzNmNWYUdmcVZpRVFBQUFBJCQAAAAAAAAAAAEAAABgjjykemMyMzM1OTYwMTMxAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAIbxfWKG8X1iV; BAIDUID=DEC37B7A6AFB4B8DCD0A4E8C1613AACF:SL=0:NR=10:FG=1; newlogin=1; WP_OFFICE_TOKEN=2392862435_121.3ac4d73d965c6990952625fa3962cee1.Y7sUJjmr2eN-zSQ86NUO3C-lG0sGOVMFRHBz4Px.T7GhHw; H_WISE_SIDS=107315_110085_176399_179348_180636_196425_197471_197711_199565_204910_208721_209568_209630_210321_211433_212296_212740_213046_213358_214115_214129_214138_214143_214802_215730_216040_216660_216847_216883_216943_217914_218248_218359_218445_218447_218456_218549_218593_218597_218620_218663_219451_219666_219671_219715_219860_219888_219943_219946_220072_220190_220299_220334_220344_220386_220610_220663_220774_221007_221016_221088_221107_221116_221119_221120_221307_221416_221501_221680_221732_221797_221825_221873_221899_221908_221958_221965_222271_222385_222397_222417_222461_222498_222522_222616_222618_222620_222625_222796_222870_223041_223065; MSA_WH=498_741; MCITY=-288%3A; BDORZ=B490B5EBF6F3CD402E515D22BCDA1598; BDSFRCVID=6-POJexroG06AkbDgpUsJIPiCeKK0gOTDYrEOwXPsp3LGJLVc8uvEG0PtsaxgF4b1DMtogKKymOTHrDF_2uxOjjg8UtVJeC6EG0Ptf8g0M5; H_BDCLCKID_SF=tJIJ_ID2JCD3H48k-4QEbbQH-UnLq-jdtgOZ04n-ah02Mljc-xOJK-CEy4rTaxJ-W20j0h7m3UTKsq76Wh35K5tTQP6rLttOb6c4KKJxbPOHJb3Dj-KaQMu3hUJiBbcLBan7QqbIXKohJh7FM4tW3J0ZyxomtfQxtNRJ0DnjtpChbC_Gj58aj5QLeU5eetjK2CntsJOOaCkMof7Oy4oWK441DajCa-jR55RXLlcYLnTTeqvoD-Jc3M04K4o9-hvT-54e2p3FBUQjb-5CQft20b0y-q7M0j3atmnuLn7jWhvBDq72yb5UQlRX5q79atTMfNTJ-qcH0KQpsIJM5-DWbT8IjHCHt60JtRPtoCvt-5rDHJTg5DTjhPrM3-oWWMT-MTryKKtK0RvDsb3xh4rkyq8tLUoiB5OMBanRh4oNB-3iV-OxDUvnyxAZ-N7t-fQxtNRJQbjy5hLMHCjeXJOobUPU2fc9LUvH3gcdot5yBbc8eIna5hjkbfJBQttjQn3hfIkj2CKLtDKMMKDmjjL3-RJH-xQ0KnLXKKOLVK_MBp7ketn4hUt2jx_f-4OMbpQ7We5TBlOHWhk2ep72QhrKQf4WWb3ebTJr32Qr-J8yb4QpsIJM5bbH2--zjMKD0UrJaKvia-TEBMb1qlbDBT5h2M4qMxtOLR3pWDTm_q5TtUJMeCnTDMFhe6j0ja8DtTLDfKresJoq2RbhKROvhj4-qq8gyxoObtRxtncCBbog5-JlqpcV0-rTbfPUDMJ9LU3k-eT9LMnx--t58h3_XhjjebLBQttjQn3eK6bMKDbttlbYfb7TyURBhf47yh5R0q4Hb6b9BJcjfU5MSlcNLTjpQT8r5MDOK5OuJRQ2QJ8BJKI-hxK; ab_sr=1.0.1_NWY0NjdlZDhjMzUxYmRiMWFjYWQ0ZGZjOWJiYzg5ZjA2OTYyNjc1NWFiYTBmYzg5MGNhOThkMmJkNjg2NWIwODE2MzQyNGQ0YjQ5NzEyMTU4Yzc5ZjZkNjkwNmUxZGRhN2I2M2Y4ZWQyZDU1NGRiNmNiODlmNWY2ZDJlZWJlZmJmODQ3ZTUzNzkzM2IzOGY3M2QzYTczMGJmM2I4NDEzMGE4MzE4MTA1ZmU4YmYzNzU0Y2UxNmU2NzIxOGZkNGJh; BDSFRCVID_BFESS=6-POJexroG06AkbDgpUsJIPiCeKK0gOTDYrEOwXPsp3LGJLVc8uvEG0PtsaxgF4b1DMtogKKymOTHrDF_2uxOjjg8UtVJeC6EG0Ptf8g0M5; H_BDCLCKID_SF_BFESS=tJIJ_ID2JCD3H48k-4QEbbQH-UnLq-jdtgOZ04n-ah02Mljc-xOJK-CEy4rTaxJ-W20j0h7m3UTKsq76Wh35K5tTQP6rLttOb6c4KKJxbPOHJb3Dj-KaQMu3hUJiBbcLBan7QqbIXKohJh7FM4tW3J0ZyxomtfQxtNRJ0DnjtpChbC_Gj58aj5QLeU5eetjK2CntsJOOaCkMof7Oy4oWK441DajCa-jR55RXLlcYLnTTeqvoD-Jc3M04K4o9-hvT-54e2p3FBUQjb-5CQft20b0y-q7M0j3atmnuLn7jWhvBDq72yb5UQlRX5q79atTMfNTJ-qcH0KQpsIJM5-DWbT8IjHCHt60JtRPtoCvt-5rDHJTg5DTjhPrM3-oWWMT-MTryKKtK0RvDsb3xh4rkyq8tLUoiB5OMBanRh4oNB-3iV-OxDUvnyxAZ-N7t-fQxtNRJQbjy5hLMHCjeXJOobUPU2fc9LUvH3gcdot5yBbc8eIna5hjkbfJBQttjQn3hfIkj2CKLtDKMMKDmjjL3-RJH-xQ0KnLXKKOLVK_MBp7ketn4hUt2jx_f-4OMbpQ7We5TBlOHWhk2ep72QhrKQf4WWb3ebTJr32Qr-J8yb4QpsIJM5bbH2--zjMKD0UrJaKvia-TEBMb1qlbDBT5h2M4qMxtOLR3pWDTm_q5TtUJMeCnTDMFhe6j0ja8DtTLDfKresJoq2RbhKROvhj4-qq8gyxoObtRxtncCBbog5-JlqpcV0-rTbfPUDMJ9LU3k-eT9LMnx--t58h3_XhjjebLBQttjQn3eK6bMKDbttlbYfb7TyURBhf47yh5R0q4Hb6b9BJcjfU5MSlcNLTjpQT8r5MDOK5OuJRQ2QJ8BJKI-hxK; delPer=0; BD_CK_SAM=1; PSINO=5; BA_HECTOR=048l2g00aga1a1agag0kv5131hen15q17; ZFY=ztZcCDJS69hYMF5oI7Ww2jxSsafjCzC7wYSQDR3eJPg:C; ZD_ENTRY=empty; channel=baidusearch; BAIDUID_BFESS=67186F289820319490C5664F5CF18725:FG=1; BD_HOME=1; sug=3; sugstore=0; ORIGIN=0; bdime=0; COOKIE_SESSION=5546_1_8_7_3_28_1_0_7_8_1_1_18964_0_0_0_1659597196_1659578202_1659602739%7C9%23154027_20_1659578197%7C9; baikeVisitId=c4a9aef9-25e5-40db-bab9-63a1cb18cbd9; H_PS_PSSID=36557_36757_36726_36413_36954_36166_36918_36919_37006_36740_36923_26350_36864_36930; BDSVRTM=56',
        'sec-ch-ua-platform': "Windows",
        'Referer': 'https://www.baidu.com/'

    }
    pattern_link=re.compile(r'<h3 class="t">.*?href="(.*?)"',re.S)
    pattern_next_page=re.compile(r'下一页',re.S)
    pattern_front_page=re.compile(r'上一页',re.S)
    pattern_page_num_first=re.compile(r'id="page">(.*?)</p>',re.S)
    cur.execute("select * from KeyWords")
    fr=cur.fetchall()
    for line in fr:
        key=line[1]
        aim=line[2]
        id=line[0]
        key_word_link[id]={}
        print("aim:"+aim+"key:"+key)
        baseUrl = 'http://www.baidu.com/s'
        page=1
        data = {'wd': aim, 'pn': str(page - 1) + '0', 'tn': 'baidurt', 'ie': 'utf-8', 'bsst': '1'}
        first_page=requests.get(baseUrl,params=data,headers=headers)#先爬一页来分析结构
        next=Judge_next_page(first_page,pattern_next_page)#判断是否有下一页
        front=Judge_front_page(first_page,pattern_front_page)#判断是否有上一页
        page_num=Get_Page_Num(pattern_page_num_first,first_page,front,next)#提取出页数  //有问题的部分
        key_word_link[id][key]=Get_Result_pages(baseUrl,page_num,pattern_link,aim,headers)
    
    return key_word_link

#判断是否有下一页
def Judge_next_page(page,pattern_next_page):
    items=re.findall(pattern_next_page,page.text)
    if(len(items)==0):
        return False
    else:
        return True
#判断是否有上一页
def Judge_front_page(page,pattern_front_page):
    items = re.findall(pattern_front_page, page.text)
    if(len(items)==0):
        return False
    else:
        return True

#提取出搜索结果的页数
def Get_Page_Num(pattern_page_num_first,page,front,next):
    aim=re.compile(r'href="(.*?)"',re.S)
    item = re.findall(pattern_page_num_first, page.text)
    str=item[0]
    result=re.findall(aim,str)
    length=len(result)
    if(front==True) and (next==True):#多了一个链接
        length=length-1
    if(length==0):#只有一页
        length=1
    return length
#提取出一页的结果，其中result={aim:{1:***,2:***,3:***}}，1,2,3代表页数
def Get_Result_pages(baseUrl,page_num,pattern_link,aim,headers):
    result={}
    result[aim]=[]
    for i in range(page_num):
        data = {'wd': aim, 'pn': str(i) + '0', 'tn': 'baidurt', 'ie': 'utf-8', 'bsst': '1'}
        page = requests.get(baseUrl, params=data, headers=headers)
        items = re.findall(pattern_link, page.text)
        result[aim]=result[aim]+items
        time.sleep(1)
    return result

def write_to_file(link,cur):
    for keyID,Other in link.items():# KeywordID , Other(Link,Keyword,Word)
        for k,pages_v in Other.items(): # Keyword , other(Word , Link)
            for w,links in pages_v.items(): # Word , Link
                for link in links:
                    cur.execute('INSERT INTO KeywordsLinks(Link,KeyWordID) VALUES ("%s","%d")' % (pymysql.converters.escape_string(link),keyID))
                    cur.connection.commit()

def get_keyword_sentence(cur):
    cur.execute("select * from KeywordsLinks")
    results = cur.fetchall()
    for result in results:
        try:
            print(result)
            link=result[1]
            LinkID=result[0]
            headers = {
                'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 '
                              '(KHTML, like Gecko) Chrome/65.0.3325.181 Safari/537.36'
            }
            cur.execute("select Word from KeyWords,KeywordsLinks where KeywordsLinks.KeyWordID= KeyWords.KeyWordID and KeywordsLinks.LinkID=(%d)" % int(LinkID))
            keyword=(cur.fetchone())[0]
            pattern=re.compile(r'.{5,20}'+keyword+r'.{5,20}',re.S)
            replace=re.compile(r'<.*?>')
            page = requests.get(link, headers=headers,timeout=1)
            page=replace.sub('',page.text)
            items=re.findall(pattern,page)
            con=""
            for item in items:
                con+=item
            print(LinkID)
            print(len(con))
            cur.execute("""UPDATE KeywordsLinks SET Content="%s" WHERE LinkID=%d""" % (pymysql.converters.escape_string(con),LinkID))#escape_string将用户的输入进行转义，防止SQL注入
            cur.connection.commit()
            time.sleep(random.random())
        except Exception:
            pass
#删除表中的空值
def delete_empty(cur):
    cur.execute("DElETE FROM KeywordsLinks WHERE Content=''")


if __name__=='__main__':
    
    #连接数据库
    # user = input("Please input your mysql user name:")
    # password = input("Please input your mysql password:")
    user  = "root"
    password = "root"
    conn = pymysql.connect(host='localhost', user=user, passwd=password, db='mysql', charset='utf8', port=3306)#默认为127.0.0.1本地主机
    cur = conn.cursor()
    cur.execute("USE BaiduResult")

    key_word_link=find_keyword_web(cur)
    # print(key_word_link)
    write_to_file(key_word_link,cur)
    get_keyword_sentence(cur)
    delete_empty(cur)
    cur.close()
    conn.close()
    